﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using log4net;

namespace MyLib {
	/// <summary>
	/// 
	/// </summary>
	public sealed class Session : IDisposable {
		/// <summary>
		/// Internal logger.
		/// </summary>
		private ILog m_logger = LogManager.GetLogger(typeof(Session));

		/// <summary>
		/// 
		/// </summary>
		private const string RowNumberColumnName = "__rowNumber";

		/// <summary>
		/// 
		/// </summary>
		private SessionFactory m_factory;

		/// <summary>
		/// Scoped transaction.
		/// </summary>
		private ITransaction m_scopedTransaction;

		/// <summary>
		/// Local transaction.
		/// </summary>
		private DbTransaction m_localTransaction;

		/// <summary>
		/// 
		/// </summary>
		private bool m_isReadOnly;

		/// <summary>
		/// 
		/// </summary>
		private bool m_useGMT;

		/// <summary>
		/// Initialize session.
		/// </summary>
		/// <param name="factory"></param>
		/// <param name="transaction"></param>
		/// <param name="isReadOnly"></param>
		/// <param name="useGMT"></param>
		internal Session(SessionFactory factory, ITransaction transaction, bool isReadOnly, bool useGMT) {
			System.Diagnostics.Debug.Assert(factory != null);

			m_factory = factory;
			m_scopedTransaction = transaction;

			if (m_scopedTransaction != null && !m_scopedTransaction.Closed) {
				m_localTransaction = null;
			} else {
				m_localTransaction = factory.CreateTransaction();
			}

			m_isReadOnly = isReadOnly;

			m_useGMT = useGMT;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="reader"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		private DataTable CreateTableFromReader(IDataReader reader, int startRowIndex = 0, int maximumRows = int.MaxValue) {
			var dataTable = InitTableFromReader(reader);

			FillTableFromReader(dataTable, reader, startRowIndex, maximumRows);

			return dataTable;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="reader"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		private void FillTableFromReader(DataTable dataTable, IDataReader reader, int startRowIndex = 0, int maximumRows = int.MaxValue) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			var fc = reader.FieldCount;

			// Skip startRowIndex rows.
			int s = 0;
			while (s < startRowIndex && reader.Read()) {
				++s;
			}

			var tp = CreateTableReaderMapping(reader, dataTable, fc);

			// Read maximumRow rows at most.
			dataTable.BeginLoadData();

			int r = 0;
			var rc = dataTable.Columns[RowNumberColumnName];
			while (r < maximumRows && reader.Read()) {
				++r;

				var row = dataTable.NewRow();

				if (rc != null) {
					row[rc] = s + r;
				}

				for (int i = 0; i < fc; ++i) {
					var v = reader.GetValue(i);
					var c = tp[i];
					if (c != null) {
						if (reader.IsDBNull(i) || v == null || v == DBNull.Value) {
							row[c] = DBNull.Value;
						} else {
							if (m_useGMT && c.DataType == typeof(DateTime)) {
								var vdt = Convert.ToDateTime(v);
								v = vdt.ToLocalTime();
							}

							row[c] = v;
						} // end of if.
					} // end of if.
				} // end of for.

				dataTable.Rows.Add(row);
			}

			dataTable.EndLoadData();

			dataTable.AcceptChanges();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="reader"></param>
		/// <returns></returns>
		private static DataTable InitTableFromReader(IDataReader reader) {
			var fc = reader.FieldCount;
			var dataTable = new DataTable();

			dataTable.BeginInit();
			var rc = dataTable.Columns.Add(RowNumberColumnName, typeof(string));
			rc.DefaultValue = "";
			rc.Unique = false;
			rc.AllowDBNull = false;
			for (int i = 0; i < fc; ++i) {
				dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
			}
			dataTable.EndInit();

			return dataTable;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="reader"></param>
		/// <param name="dataTable"></param>
		/// <param name="fc"></param>
		/// <returns></returns>
		private static DataColumn[] CreateTableReaderMapping(IDataReader reader, DataTable dataTable, int fc) {
			var tp = new DataColumn[fc];
			for (int i = 0; i < fc; ++i) {
				tp[i] = dataTable.Columns[reader.GetName(i)];
			}
			return tp;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		private DbTransaction GetTransaction() {
			if (m_scopedTransaction != null && !m_scopedTransaction.Closed) {
				return m_scopedTransaction.Transaction;
			} else {
				return m_localTransaction;
			}
		}

		/// <summary>
		/// Execute query.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DataTable ExecuteQuery(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			DataTable dataTable;

			using (var reader = command.ExecuteReader()) {
				dataTable = CreateTableFromReader(reader);
			}

			GetOutputParameterValues(command, parameters);

			return dataTable;
		}

		/// <summary>
		/// Execute query.
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		public void ExecuteQuery(System.Data.DataTable dataTable, string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			using (var reader = command.ExecuteReader()) {
				FillTableFromReader(dataTable, reader);
			}

			GetOutputParameterValues(command, parameters);
		}

		/// <summary>
		/// Execute single query.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DataTable ExecuteSingleQuery(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			return ExecutePagedQuery(sql, commandType, 0, 1, parameters);
		}

		/// <summary>
		/// Execute single query.
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		public void ExecuteSingleQuery(System.Data.DataTable dataTable, string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			ExecutePagedQuery(dataTable, sql, commandType, 0, 1, parameters);
		}

		/// <summary>
		/// Execute paged query.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		public System.Data.DataTable ExecutePagedQuery(string sql, System.Data.CommandType commandType, int startRowIndex, int maximumRows, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			DataTable dataTable;

			using (var reader = command.ExecuteReader()) {
				dataTable = CreateTableFromReader(reader, startRowIndex, maximumRows);
			}

			GetOutputParameterValues(command, parameters);

			return dataTable;
		}

		/// <summary>
		/// Execute paged query.
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		public void ExecutePagedQuery(System.Data.DataTable dataTable, string sql, System.Data.CommandType commandType, int startRowIndex, int maximumRows, params DbParameter[] parameters) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			if (dataTable.Columns[RowNumberColumnName] == null) {
				dataTable.BeginInit();
				var rc = dataTable.Columns.Add(RowNumberColumnName, typeof(string));
				rc.DefaultValue = "";
				rc.Unique = false;
				rc.AllowDBNull = false;
				dataTable.EndInit();
			}

			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			using (var reader = command.ExecuteReader()) {
				FillTableFromReader(dataTable, reader, startRowIndex, maximumRows);
			}

			GetOutputParameterValues(command, parameters);
		}

		/// <summary>
		/// Execute query and returns a list.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public IList<T> ExecuteList<T>(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			var list = new List<T>();

			using (var reader = command.ExecuteReader()) {
				while (reader.Read()) {
					var v = reader.GetValue(0);

					if (m_useGMT && reader.GetFieldType(0) == typeof(DateTime)) {
						v = Convert.ToDateTime(v).ToLocalTime();
					}

					list.Add((T)Convert.ChangeType(v, typeof(T)));
				}
			}

			GetOutputParameterValues(command, parameters);

			return list;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="command"></param>
		/// <param name="parameters"></param>
		private void GetOutputParameterValues(DbCommand command, params DbParameter[] parameters) {
			for (int i = 0; i < parameters.Length; ++i) {
				var parameter = parameters[i];

				if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput) {
					var v = command.Parameters[i].Value;
					if (v == null || v == DBNull.Value) {
						parameter.Value = DBNull.Value;
					} else {
						if (m_useGMT && (parameter.DbType == DbParameterType.DateTime
							|| parameter.DbType == DbParameterType.EndDate || parameter.DbType == DbParameterType.EndMonth
							|| parameter.DbType == DbParameterType.EndYear)) {
							var vdt = Convert.ToDateTime(v);

							v = vdt.ToLocalTime();
						}

						parameter.Value = command.Parameters[i].Value;
					} // end of if.
				} // end of if.
			} // end of for.
		}

		/// <summary>
		/// Execute query and returns a scalar value.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public T ExecuteScalar<T>(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			var v = command.ExecuteScalar();

			if (m_useGMT && v is DateTime) {
				v = Convert.ToDateTime(v).ToLocalTime();
			}

			GetOutputParameterValues(command, parameters);

			return (T)Convert.ChangeType(v, typeof(T));
		}

		/// <summary>
		/// Execute update.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public int ExecuteUpdate(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			var r = command.ExecuteNonQuery();
			GetOutputParameterValues(command, parameters);

			return r;
		}

		/// <summary>
		/// Execute reader.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbDataReader ExecuteReader(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Transaction = GetTransaction();

			return command.ExecuteReader();
		}

		/// <summary>
		/// Commit session.
		/// </summary>
		public void Commit() {
			// Commit local transaction.
			if (m_localTransaction != null) {
				if (m_logger.IsDebugEnabled) {
					m_logger.DebugFormat("Commit a transaction.");
				}
				try {
					m_localTransaction.Commit();
				} finally {
					m_localTransaction = null;
				}
			}
		}

		/// <summary>
		/// Close session.
		/// </summary>
		public void Close() {
			// Rollback any transaction.
			if (m_localTransaction != null) {
				if (m_logger.IsDebugEnabled) {
					m_logger.DebugFormat("Rollback a transaction.");
				}
				try {
					m_localTransaction.Rollback();
				} finally {
					m_localTransaction = null;
				}
			}
		}

		/// <summary>
		/// Release all resources.
		/// </summary>
		public void Dispose() {
			Close();
		} // end of Dispose.
	} // end of Session.
}
